-- =========================================================================
-- Title: Run audit rules for a single server or all servers
-- Author: Nem W. Schlecht
-- Create Date: 2013-07-01
-- License: Creative Commons Attribution-Share Alike
-- Description: Stored procedure to run the rule checks for a given server
--     or for all servers.
-- =========================================================================
USE ServerAudit;
GO

--
-- Database: ServerAudit
--   Schema: dbo
--     Name: checkRules
--
IF (
		EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'P'
				AND NAME = 'checkRules'
			)
		)
BEGIN
	DROP PROCEDURE checkRules;
END;
GO

CREATE PROCEDURE [dbo].[checkRules]
	@ReportServerName VARCHAR(200) = NULL
	, @checkDate DATETIME = NULL
	, @htmloutput BINARY = 0
	, @debug TINYINT = 0
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ruleId INT;
	DECLARE @runOrder INT;
	DECLARE @ruleName VARCHAR(100);
	-- DECLARE @ruleSystems VARCHAR(1000);
	-- DECLARE @ruleExclude VARCHAR(1000);
	DECLARE @ruleAction VARCHAR(100);
	DECLARE @ruleConfigKey VARCHAR(100);
	DECLARE @ruleTarget VARCHAR(100);
	DECLARE @lastRowCount INT;
	DECLARE @histId INT;

	IF (@checkDate IS NULL)
	BEGIN
		SELECT @histId = MAX(histId)
		FROM auditHistory;
	END
	ELSE
	BEGIN
		SELECT @histId = MAX(histId)
		FROM auditHistory
		WHERE CAST(histDate AS DATE) = @checkDate
	END;

	DECLARE rules_list_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
	FOR
	SELECT ruleId
		, runOrder
		, ruleName
		, ACTION
		, configKey
		, target
	FROM auditRules
	ORDER BY runOrder
;

	OPEN rules_list_cursor;

	FETCH NEXT
	FROM rules_list_cursor
	INTO @ruleId
		, @runOrder
		, @ruleName
		, @ruleAction
		, @ruleConfigKey
		, @ruleTarget
	;

	--
	-- Each check will add entries to this table
	DECLARE @Report TABLE (
		ruleId INT NOT NULL
		, runOrder INT NOT NULL
		, RESULT VARCHAR(100) NOT NULL
		, SERVERNAME VARCHAR(200) NOT NULL
		, ikey VARCHAR(200) NOT NULL
		, ivalue VARCHAR(MAX) NULL
		)
	;

	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		IF (@debug > 0)
		BEGIN
			PRINT 'RuleID: '
				+ CAST(@ruleID AS VARCHAR(4))
				+ CHAR(10)
				+ '  RuleName: '
				+ @ruleName
				+ CHAR(10)
				+ '  RuleAction: '
				+
				+ @ruleAction
				+ CHAR(10)
				+ '  RuleConfigKey: '
				+
				+ CAST(@ruleConfigKey AS VARCHAR(40))
				+ CHAR(10)
				+ '  RuleTarget: '
				+ @ruleTarget;
		END;

		-- Determine if we have an INCLUDE list or need to check all systems
		-- 0 is all, >1, just those systems
		DECLARE @SrvIncCount INT;

		SET @SrvIncCount = (
				SELECT COUNT(1)
				FROM auditRuleIncludes
				WHERE ruleId = @ruleId
				)
			;

		IF (@debug > 0)
		BEGIN
			DECLARE @SrvExcCount INT;

			SET @SrvExcCount = (
					SELECT COUNT(1)
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				;

			PRINT 'Include count: ' + CAST(@SrvIncCount AS VARCHAR(3));
			PRINT 'Exclude count: ' + CAST(@SrvExcCount AS VARCHAR(3));
		END;

		IF (@ruleAction = 'equals')
		BEGIN
			IF (@debug > 0)
			BEGIN
				PRINT 'Doing comparison: '
					+ @ruleConfigKey
					+ ' ? '
					+ @ruleTarget;
			END;

			-- Find if missing
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'equals:variable missing'
				, missingVarServers.s1
				, @ruleConfigKey
				, NULL
			FROM (
				SELECT ad1.ServerName AS s1
					, ad2.ServerName AS s2
				FROM auditData AS ad1
					LEFT JOIN auditData AS ad2
						ON ad1.ServerName = ad2.ServerName
							AND ad2.ikey LIKE @ruleConfigKey
							AND ad2.histId = @histId
				WHERE ad2.ServerName IS NULL
					AND ad1.histId = @histId
				GROUP BY ad1.ServerName
					, ad2.ServerName
				) AS missingVarServers
			WHERE missingVarServers.s1 NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR missingVarServers.s1 IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			ORDER BY missingVarServers.s1
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' missing entries';
			END;

			INSERT INTO @Report
			SELECT @ruleID AS ruleID
				, @runOrder
				, 'equals:no match' AS 'result'
				, SERVERNAME
				, ikey
				, ivalue
			FROM auditData AS ad
			WHERE ikey LIKE @ruleConfigKey
				AND ivalue <> @ruleTarget
				AND ad.histId = @histId
				AND SERVERNAME NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR SERVERNAME IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' matching entries';
				PRINT '';
			END;
		END;

		IF (@ruleAction = 'notequals')
		BEGIN
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'notequals:variable missing'
				, missingVarServers.s1
				, @ruleConfigKey
				, NULL
			FROM (
				SELECT ad1.ServerName AS s1
					, ad2.ServerName AS s2
				FROM auditData AS ad1
					LEFT JOIN auditData AS ad2
						ON ad1.ServerName = ad2.ServerName
							AND ad2.ikey LIKE @ruleConfigKey
							AND ad2.histId = @histId
				WHERE ad2.ServerName IS NULL
					AND ad1.histId = @histId
				GROUP BY ad1.ServerName
					, ad2.ServerName
				) AS missingVarServers
			WHERE missingVarServers.s1 NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR missingVarServers.s1 IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			ORDER BY missingVarServers.s1
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' missing entries';
			END;

			INSERT INTO @Report
			SELECT @ruleID AS ruleID
				, @runOrder
				, 'notequals:match' AS 'result'
				, SERVERNAME
				, ikey
				, ivalue
			FROM auditData AS ad
			WHERE ikey LIKE @ruleConfigKey
				AND ad.histId = @histId
				AND ivalue = @ruleTarget
				AND SERVERNAME NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR SERVERNAME IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' matching entries';
				PRINT '';
			END;
		END;
		ELSE IF (@ruleAction = 'contains')
		BEGIN
			IF (@debug > 0)
			BEGIN
				PRINT 'Does it contain? '
					+ @ruleConfigKey
					+ ' ? '
					+ @ruleTarget;
			END;

			-- Find if missing
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'contains:variable missing'
				, missingVarServers.s1
				, @ruleConfigKey
				, NULL
			FROM (
				SELECT ad1.ServerName AS s1
					, ad2.ServerName AS s2
				FROM auditData AS ad1
					LEFT JOIN auditData AS ad2
						ON ad1.ServerName = ad2.ServerName
							AND ad2.ikey LIKE @ruleConfigKey
							AND ad2.histId = @histId
				WHERE ad2.ServerName IS NULL
					AND ad1.histId = @histId
				GROUP BY ad1.ServerName
					, ad2.ServerName
				) AS missingVarServers
			WHERE missingVarServers.s1 NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR missingVarServers.s1 IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			ORDER BY missingVarServers.s1
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' missing entries';
			END;

			-- Actually process
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'contains:no match'
				, SERVERNAME
				, ikey
				, ivalue
			FROM auditData AS ad
			WHERE ikey LIKE @ruleConfigKey
				AND ad.histId = @histId
				AND ivalue NOT LIKE '%'
					+ @ruleTarget
					+ '%'
				AND SERVERNAME NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR SERVERNAME IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' matching entries';
				PRINT '';
			END;
		END;
		ELSE IF (@ruleAction = 'notcontains')
		BEGIN
			IF (@debug > 0)
			BEGIN
				PRINT 'Does it NOT contain? '
					+ @ruleConfigKey
					+ ' ? '
					+ @ruleTarget;
			END;

			-- Find if missing
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'notcontains:variable missing'
				, missingVarServers.s1
				, @ruleConfigKey
				, NULL
			FROM (
				SELECT ad1.ServerName AS s1
					, ad2.ServerName AS s2
				FROM auditData AS ad1
					LEFT JOIN auditData AS ad2
						ON ad1.ServerName = ad2.ServerName
							AND ad2.ikey LIKE @ruleConfigKey
							AND ad2.histId = @histId
				WHERE ad2.ServerName IS NULL
					AND ad1.histId = @histId
				GROUP BY ad1.ServerName
					, ad2.ServerName
				) AS missingVarServers
			WHERE missingVarServers.s1 NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR missingVarServers.s1 IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			ORDER BY missingVarServers.s1
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' missing entries';
			END;

			-- Actually process
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'notcontains:match'
				, SERVERNAME
				, ikey
				, ivalue
			FROM auditData AS ad
			WHERE ikey LIKE @ruleConfigKey
				AND histId = @histId
				AND ivalue LIKE '%'
					+ @ruleTarget
					+ '%'
				AND SERVERNAME NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR SERVERNAME IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' matching entries';
				PRINT '';
			END;
		END;
		ELSE IF (@ruleAction = 'notnull')
		BEGIN
			IF (@debug > 0)
			BEGIN
				PRINT 'Is it not null? '
					+ @ruleConfigKey
					+ ' ? '
					+ @ruleTarget;
			END;

			-- Find if missing
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'notnull:variable missing'
				, missingVarServers.s1
				, @ruleConfigKey
				, NULL
			FROM (
				SELECT ad1.ServerName AS s1
					, ad2.ServerName AS s2
				FROM auditData AS ad1
					LEFT JOIN auditData AS ad2
						ON ad1.ServerName = ad2.ServerName
							AND ad2.ikey LIKE @ruleConfigKey
							AND ad2.histId = @histId
				WHERE ad2.ServerName IS NULL
					AND ad1.histId = @histId
				GROUP BY ad1.ServerName
					, ad2.ServerName
				) AS missingVarServers
			WHERE missingVarServers.s1 NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR missingVarServers.s1 IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			ORDER BY missingVarServers.s1
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' missing entries';
			END;

			-- Actually process
			INSERT INTO @Report
			SELECT @ruleID
				, @runOrder
				, 'notnull:match'
				, SERVERNAME
				, ikey
				, ivalue
			FROM auditData AS ad
			WHERE ikey LIKE @ruleConfigKey
				AND ivalue IS NULL
				AND histId = @histId
				AND SERVERNAME NOT IN (
					SELECT systemExclude
					FROM auditRuleExcludes
					WHERE ruleId = @ruleId
					)
				AND (
					@SrvIncCount = 0
					OR SERVERNAME IN (
						SELECT systemInclude
						FROM auditRuleIncludes
						WHERE ruleId = @ruleId
						)
					)
			;

			SET @lastRowCount = @@ROWCOUNT;

			IF (@debug > 0)
			BEGIN
				PRINT 'Found '
					+ CAST(@lastRowCount AS VARCHAR(4))
					+ ' matching entries';
				PRINT '';
			END;
		END;

		FETCH NEXT
		FROM rules_list_cursor
		INTO @ruleId
			, @runOrder
			, @ruleName
			, @ruleAction
			, @ruleConfigKey
			, @ruleTarget
		;
	END;

	CLOSE rules_list_cursor;

	DEALLOCATE rules_list_cursor;

	DECLARE @rescount INT;

	SELECT @rescount = COUNT(1)
	FROM @Report;

	IF (@rescount > 0)
	BEGIN
		IF (@htmloutput = 1)
		BEGIN
			PRINT '<html>';
			PRINT '<head>';
			PRINT '<title>';
			PRINT 'SQL Audit Rule Checks';
			PRINT '</title>';
			PRINT '</head>';
			PRINT '<body>';
			PRINT '<h1 style="text-align: center">';
			PRINT 'SQL Audit Rule Checks';
			PRINT '</h1>';

			SELECT '<table border=1 cellpadding=3 style="min-width:50%">'
				+ '<tr><td align="right">Rule:</td><td>'
				+ ar.ruleName
				+ '</td>'
				+ '</tr>'
				+ CHAR(10)
				+ '</tr>'
				+ '<td align="right">Result:</td><td>'
				+ rep.result
				+ '</td>'
				+ '</tr>'
				+ CHAR(10)
				+ '<tr><td align="right">Instance:</td><td>'
				+ rep.ServerName
				+ '</td>'
				+ '</tr>'
				+ CHAR(10)
				+ '<tr><td align="right">Variable:</td><td>'
				+ rep.ikey
				+ '</td>'
				+ '</tr>'
				+ CHAR(10)
				+ '<tr><td align="right">Value:</td><td>'
				+ REPLACE(ISNULL(rep.ivalue, '(NULL)'), ',', ',<br>')
				+ '</td>'
				+ '</tr>'
				+ CHAR(10)
				+ '</table><br>'
				 AS '<!-- Results Table -->'
			FROM @Report AS rep
				JOIN auditRules AS ar
					ON ar.ruleId = rep.ruleId
			WHERE (
					@ReportServerName IS NULL
					OR rep.ServerName = @ReportServerName
					)
			ORDER BY rep.runOrder
				, rep.ikey
				, rep.ServerName
			;

			PRINT '</table>';
		END;
		ELSE
		BEGIN
			SELECT '-- Rule Checks --' + CHAR(10);

			SELECT '    Rule: '
				+ ar.ruleName
				+ CHAR(10)
				+ '  Result: '
				+ rep.result
				+ CHAR(10)
				+ 'Instance: '
				+ rep.ServerName
				+ CHAR(10)
				+ 'Variable: '
				+ rep.ikey
				+ CHAR(10)
				+ '   Value: '
				+ ISNULL(rep.ivalue, '(NULL)')
				+ CHAR(10) AS '-- '
			FROM @Report AS rep
				JOIN auditRules AS ar
					ON ar.ruleId = rep.ruleId
			WHERE (
					@ReportServerName IS NULL
					OR rep.ServerName = @ReportServerName
					)
			ORDER BY rep.runOrder
				, rep.ikey
				, rep.ServerName
			;
		END;


		IF (@htmloutput = 1)
		BEGIN
			PRINT '<pre>';
			SELECT '--'
				+ CHAR(10)
				+ '-- Rule Correction code '
				+ '(run in ''sqlcmd'' mode to repair issues):'
				+ CHAR(10)
				+ '--'
				+ CHAR(10)
				AS '-- '
			;

			-- Print SQL to correct issues
			SELECT '-- Fix '
				+ ar.rulename
				+ '--'
				+ ar.action
				+ '--'
				+ ar.configKey
				+ CHAR(10)
				+ ':connect '
				+ rep.ServerName
				+ CHAR(10)
				+ 'IF (@@SERVERNAME <> '''
				+ rep.ServerName
				+ ''')'
				+ CHAR(10)
				+ 'BEGIN'
				+ CHAR(10)
				+ '    PRINT '' *** Wrong Server! '' '
				+ CHAR(10)
				+ '        + @@SERVERNAME'
				+ CHAR(10)
				+ '        + '' <> '
				+ rep.ServerName
				+ ''';'
				+ CHAR(10)
				+ '    SET NOEXEC ON;'
				+ CHAR(10)
				+ 'END'
				+ CHAR(10)
				+ arf.ruleSQL
				+ CHAR(10)
				+ 'GO'
				+ CHAR(10)
				+ '-- End fix '
				+ ar.rulename
				+ '--'
				+ ar.action
				+ '--'
				+ ar.configKey
				+ CHAR(10)
				+ CHAR(10)
				AS '-- '
			FROM @Report AS rep
				JOIN auditRules AS ar
					ON ar.ruleId = rep.ruleId
				JOIN auditRuleFix AS arf
					ON ar.ruleId = arf.ruleId
			ORDER BY rep.runOrder
			;
			SET @lastRowCount = @@ROWCOUNT;
			PRINT '</pre>';
		END;
		ELSE
		BEGIN
			SELECT '--'
				+ CHAR(10)
				+ '-- Rule Correction code '
				+ '(run in ''sqlcmd'' mode to repair issues):'
				+ CHAR(10)
				+ '--'
				+ CHAR(10)
				AS '-- '
			;

			-- Print SQL to correct issues
			SELECT '-- Fix '
				+ ar.rulename
				+ '--'
				+ ar.action
				+ '--'
				+ ar.configKey
				+ CHAR(10)
				+ ':connect '
				+ rep.ServerName
				+ CHAR(10)
				+ 'IF (@@SERVERNAME <> '''
				+ rep.ServerName
				+ ''')'
				+ CHAR(10)
				+ 'BEGIN'
				+ CHAR(10)
				+ '    PRINT '' *** Wrong Server! '' '
				+ CHAR(10)
				+ '        + @@SERVERNAME'
				+ CHAR(10)
				+ '        + '' <> '
				+ rep.ServerName
				+ ''';'
				+ CHAR(10)
				+ '    SET NOEXEC ON;'
				+ CHAR(10)
				+ 'END'
				+ CHAR(10)
				+ arf.ruleSQL
				+ CHAR(10)
				+ 'GO'
				+ CHAR(10)
				+ '-- End fix '
				+ ar.rulename
				+ '--'
				+ ar.action
				+ '--'
				+ ar.configKey
				+ CHAR(10)
				+ CHAR(10)
				AS '-- '
			FROM @Report AS rep
				JOIN auditRules AS ar
					ON ar.ruleId = rep.ruleId
				JOIN auditRuleFix AS arf
					ON ar.ruleId = arf.ruleId
			ORDER BY rep.runOrder
			;
			SET @lastRowCount = @@ROWCOUNT;
		END;

		IF (@lastRowCount = 0)
		BEGIN
			IF (@htmloutput = 1)
			BEGIN
				SELECT '<h1>***** No code fixes available/needed *****</h1>' AS ' ';
			END;
			ELSE
			BEGIN
				SELECT '-- ***** No code fixes available/needed *****' AS ' ';
			END;
		END;
	END;
	ELSE
	BEGIN
		SELECT '-- ***** All rules passed checks  - '
			+ CAST(@checkDate AS VARCHAR(120))
			+ ' *****' AS ' ';
	END;


	IF (@htmloutput = 1)
	BEGIN
		PRINT '</body>';
		PRINT '</html>';
	END;

	
END;
GO

--EXEC dbo.checkRules
--	@checkDate = '2014-03-15'
--	, @debug = 0
--;

--EXEC dbo.checkRules;
--GO
